package com.rui1024.dao.impl;

import com.rui1024.dao.DiaryTypeDao;
import com.rui1024.pojo.DiaryType;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class DiaryTypeDaoImpl implements DiaryTypeDao {

    @Override
    public List<DiaryType> diaryTypeCountList(Connection con) throws Exception {
        String sql="SELECT t2.diaryTypeId,t2.typeName,COUNT(diaryId) as diaryCount FROM t_diary t1 RIGHT JOIN  t_diaryType t2 ON t1.`typeId`=t2.`diaryTypeId` GROUP BY t2.`typeName`";
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<DiaryType> diaryTypeCountList = new ArrayList<>();
        while (rs.next()){
            DiaryType diaryType = new DiaryType();
            diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
            diaryType.setTypeName(rs.getString("typeName"));
            diaryType.setDiaryCount(rs.getInt("diaryCount"));
            diaryTypeCountList.add(diaryType);
        }
        return diaryTypeCountList;
    }

    @Override
    public List<DiaryType> diaryTypeList(Connection con) throws Exception {
        String sql="select * from t_diaryType";
        PreparedStatement pstmt = con.prepareStatement(sql);
        ArrayList<DiaryType> diaryTypeList = new ArrayList<>();
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()){
            DiaryType diaryType = new DiaryType();
            diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
            diaryType.setTypeName(rs.getString("typeName"));
            diaryTypeList.add(diaryType);
        }
        return diaryTypeList;
    }


    @Override
    public int diaryAdd(Connection con, DiaryType diaryType) throws Exception {
        String sql="insert into t_diaryType values(null,?)";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,diaryType.getTypeName());
        return pstmt.executeUpdate();
    }

    @Override
    public DiaryType diaryTypeShow(Connection con, int diaryTypeId) throws Exception {
        String sql="select * from t_diaryType where diaryTypeId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,diaryTypeId);
        ResultSet rs = pstmt.executeQuery();
        DiaryType diaryType=null;
        while (rs.next()){
            diaryType = new DiaryType();
            diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
            diaryType.setTypeName(rs.getString("typeName"));
        }
        return diaryType;
    }

    @Override
    public int diaryUpdate(Connection con, DiaryType diaryType) throws Exception {
        String sql="update t_diaryType set typeName=? where diaryTypeId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,diaryType.getTypeName());
        pstmt.setInt(2,diaryType.getDiaryTypeId());
        return pstmt.executeUpdate();
    }

    @Override
    public int diaryTypeDelete(Connection con, int diaryTypeId) throws Exception {
        String sql="delete from t_diaryType where diaryTypeId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,diaryTypeId);
        return pstmt.executeUpdate();
    }
}
